SherbyNet - /page/wikibook/entry.mu

Micron Document

<!DOCTYPE html>

<title>PL/pgSQL</title>

</head>

PL/pgSQL

</h1>

</header>

/* start https://en.wikipedia.org/ */

.mw-parser-output .infobox-subbox{padding:0;border:none;margin:-3px;width:auto;min-width:100%;font-size:100%;clear:none;float:none;background-color:transparent}.mw-parser-output .infobox-3cols-child{margin:auto}.mw-parser-output .infobox .navbar{font-size:100%}@media screen{html.skin-theme-clientpref-night .mw-parser-output .infobox-full-data:not(.notheme)>div:not(.notheme)[style]{background:#1f1f23!important;color:#f8f9fa}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .infobox-full-data:not(.notheme)>div:not(.notheme)[style]{background:#1f1f23!important;color:#f8f9fa}}@media(min-width:640px){body.skin--responsive .mw-parser-output .infobox-table{display:table!important}body.skin--responsive .mw-parser-output .infobox-table>caption{display:table-caption!important}body.skin--responsive .mw-parser-output .infobox-table>tbody{display:table-row-group}body.skin--responsive .mw-parser-output .infobox-table th,body.skin--responsive .mw-parser-output .infobox-table td{padding-left:inherit;padding-right:inherit}}

/* end https://en.wikipedia.org/ */

</style><table class="infobox vevent"><tbody><tr><th colspan="2" class="infobox-above" style="background-color:#e0e0e0;">PL/pgSQL</th></tr><tr><td colspan="2" class="infobox-image"></td></tr><tr><th scope="row" class="infobox-label"><a href="../Software_design" title="Software design">Designed by</a></th><td class="infobox-data">Jan Wieck</td></tr><tr><th scope="row" class="infobox-label"><a href="../Software_developer" class="mw-redirect" title="Software developer">Developer</a></th><td class="infobox-data organiser">PostgreSQL Global Development Group</td></tr><tr><th scope="row" class="infobox-label">First appeared</th><td class="infobox-data">October 30, 1998 (1998-10-30)</td></tr><tr><th scope="row" class="infobox-label">Website</th><td class="infobox-data"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/plpgsql.html">www.postgresql.org/docs/current/static/plpgsql.html</a></td></tr><tr><th colspan="2" class="infobox-header" style="background-color: #EEEEEE;">Influenced by</th></tr><tr><td colspan="2" class="infobox-full-data"><a href="../PL/SQL" title="PL/SQL">PL/SQL</a>, <a href="../Ada_(programming_language)" title="Ada (programming language)">Ada</a></td></tr></tbody></table>

PL/pgSQL (Procedural Language/PostgreSQL) is a <a href="../Procedural_programming" title="Procedural programming">procedural</a> <a href="../Programming_language" title="Programming language">programming language</a> supported by the <a href="../PostgreSQL" title="PostgreSQL">PostgreSQL</a> <a href="../ORDBMS" class="mw-redirect" title="ORDBMS">ORDBMS</a>. It closely resembles <a href="../Oracle_Database" title="Oracle Database">Oracle</a>'s <a href="../PL/SQL" title="PL/SQL">PL/SQL</a> language. Implemented by Jan Wieck, PL/pgSQL first appeared with PostgreSQL 6.4, released on October 30, 1998.<a href="#cite_note-1">[1]</a> Version 9 also implements some ISO <a href="../SQL/PSM" title="SQL/PSM">SQL/PSM</a> features, like overloading of SQL-invoked functions and procedures.<a href="#cite_note-2">[2]</a>

PL/pgSQL, as a fully featured programming language, allows much more procedural control than <a href="../SQL" title="SQL">SQL</a>, including the ability to use loops and other control structures. SQL statements and <a href="../Database_trigger" title="Database trigger">triggers</a> can call functions created in the PL/pgSQL language.

The design of PL/pgSQL aimed to allow PostgreSQL users to perform more complex operations and computations than SQL, while providing ease of use. The language is able to be defined as trusted by the server.<a href="#cite_note-3">[3]</a>

PL/pgSQL is one of the programming languages included in the standard PostgreSQL distribution,<a href="#cite_note-4">[4]</a> the others being <a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/pltcl.html">PL/Tcl</a>, PL/Perl<a href="#cite_note-5">[5]</a> and PL/Python.<a href="#cite_note-6">[6]</a> In addition, many others are available from third parties, including PL/Java,<a href="#cite_note-7">[7]</a> PL/pgPSM,<a href="#cite_note-PSM-8">[8]</a> PL/php,<a href="#cite_note-9">[9]</a> PL/R,<a href="#cite_note-10">[10]</a> PL/Ruby,<a href="#cite_note-11">[11]</a>

<a rel="nofollow" class="external text" href="https://github.com/petere/plsh">PL/sh</a>,

<a rel="nofollow" class="external text" href="https://github.com/pllua/pllua">PL/Lua</a>, Postmodern<a href="#cite_note-12">[12]</a> (based on <a href="../Common_Lisp" title="Common Lisp">Common Lisp</a>) and <a rel="nofollow" class="external text" href="https://pgxn.org/dist/plv8/doc/plv8.html">PL/v8</a>. PostgreSQL uses <a href="../GNU_bison" class="mw-redirect" title="GNU bison">Bison</a> as its parser,<a href="#cite_note-13">[13]</a>

making it easy to port many <a href="../Open-source_model" class="mw-redirect" title="Open-source model">open-source</a> languages, as well as to <a href="../Code_reuse" title="Code reuse">reuse code</a>.

<div class="mw-heading mw-heading2"><h2 id="Comparing_with_PSM">Comparing with PSM</h2></div>

The <a href="../SQL/PSM" title="SQL/PSM">SQL/PSM</a> language is specified by an ISO standard, but is also inspired by Oracle's PL/SQL and PL/pgPL/SQL, so there are few differences. The PL/pgPSM contributed module implements the standard. The main features of PSM that differ from PL/pgSQL:<a href="#cite_note-PSM-8">[8]</a><a href="#cite_note-proposal-14">[14]</a>

<ul><li>Exception handlers are subroutines (continue handlers);</li>

<li>Warnings can be handled as an exception;</li>

<li>Declaration of variables should be based on SQL query results.</li></ul>

All three languages (Oracle PL/SQL,<a href="#cite_note-15">[15]</a> PostgreSQL PL/pgSQL and ISO SQL/PSM<a href="#cite_note-16">[16]</a>) were heavily influenced by the <a href="../Ada_(programming_language)" title="Ada (programming language)">Ada programming language</a>.

<div class="mw-heading mw-heading2"><h2 id="Example">Example</h2></div>

The following example is a function that computes the sales tax of a given subtotal:<div class="mw-highlight mw-highlight-lang-plpgsql mw-content-ltr" dir="ltr"><pre>CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$

BEGIN

RETURN subtotal * 0.06;

END;

$$ LANGUAGE plpgsql;

</pre></div>

<div class="mw-heading mw-heading2"><h2 id="References">References</h2></div>

/* start https://en.wikipedia.org/ */

.mw-parser-output .reflist{margin-bottom:0.5em;list-style-type:decimal}@media screen{.mw-parser-output .reflist{font-size:90%}}.mw-parser-output .reflist .references{font-size:100%;margin-bottom:0;list-style-type:inherit}.mw-parser-output .reflist-columns-2{column-width:30em}.mw-parser-output .reflist-columns-3{column-width:25em}.mw-parser-output .reflist-columns{margin-top:0.3em}.mw-parser-output .reflist-columns ol{margin-top:0}.mw-parser-output .reflist-columns li{page-break-inside:avoid;break-inside:avoid-column}.mw-parser-output .reflist-upper-alpha{list-style-type:upper-alpha}.mw-parser-output .reflist-upper-roman{list-style-type:upper-roman}.mw-parser-output .reflist-lower-alpha{list-style-type:lower-alpha}.mw-parser-output .reflist-lower-greek{list-style-type:lower-greek}.mw-parser-output .reflist-lower-roman{list-style-type:lower-roman}

/* end https://en.wikipedia.org/ */

</style><div class="reflist">

/* start https://en.wikipedia.org/ */

.mw-parser-output cite.citation{font-style:inherit;word-wrap:break-word}.mw-parser-output .citation q{quotes:"\"""\"""'""'"}.mw-parser-output .citation:target{background-color:rgba(0,127,255,0.133)}.mw-parser-output .id-lock-free.id-lock-free a{background:url("../mw/Lock-green.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-limited.id-lock-limited a,.mw-parser-output .id-lock-registration.id-lock-registration a{background:url("../mw/Lock-gray-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .id-lock-subscription.id-lock-subscription a{background:url("../mw/Lock-red-alt-2.svg")right 0.1em center/9px no-repeat}.mw-parser-output .cs1-ws-icon a{background:url("../mw/Wikisource-logo.svg")right 0.1em center/12px no-repeat}body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-free a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-limited a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-registration a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .id-lock-subscription a,body:not(.skin-timeless):not(.skin-minerva) .mw-parser-output .cs1-ws-icon a{background-size:contain;padding:0 1em 0 0}.mw-parser-output .cs1-code{color:inherit;background:inherit;border:none;padding:inherit}.mw-parser-output .cs1-hidden-error{display:none;color:var(--color-error,#d33)}.mw-parser-output .cs1-visible-error{color:var(--color-error,#d33)}.mw-parser-output .cs1-maint{display:none;color:#085;margin-left:0.3em}.mw-parser-output .cs1-kern-left{padding-left:0.2em}.mw-parser-output .cs1-kern-right{padding-right:0.2em}.mw-parser-output .citation .mw-selflink{font-weight:inherit}@media screen{.mw-parser-output .cs1-format{font-size:95%}html.skin-theme-clientpref-night .mw-parser-output .cs1-maint{color:#18911f}}@media screen and (prefers-color-scheme:dark){html.skin-theme-clientpref-os .mw-parser-output .cs1-maint{color:#18911f}}

/* end https://en.wikipedia.org/ */

</style><cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/release-6-4.html">"PostgreSQL Documentation, Appendix E: Release Notes, Release 6.4"</a>. PostgreSQL Global Development Group. January 2012.</cite>

</li>

<li id="cite_note-2"><a href="#cite_ref-2">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/9.0/static/features-sql-standard.html">"feature T322"</a>, SQL standard features (9 ed.), PostgreSQL</cite>.

</li>

<li id="cite_note-3"><a href="#cite_ref-3">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/plpgsql.html">"PL/pgSQL – SQL Procedural Language"</a>. PostgreSQL. Retrieved 2007-11-15.</cite>

</li>

<li id="cite_note-4"><a href="#cite_ref-4">^</a> <cite class="citation web cs1"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/xplang.html">"Procedural Languages"</a>. 9 May 2024.</cite>

</li>

<li id="cite_note-5"><a href="#cite_ref-5">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/plperl.html">"PL/Perl"</a>, Docs (current ed.), PostgreSQL</cite>.

</li>

<li id="cite_note-6"><a href="#cite_ref-6">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/plpython.html">"PL/Python"</a>, Docs (current ed.), PostgreSQL</cite>.

</li>

<li id="cite_note-7"><a href="#cite_ref-7">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://tada.github.io/pljava/">"PL/Java"</a>, Gborg (project), PostgreSQL</cite>.

</li>

<li id="cite_note-PSM-8">^ <a href="#cite_ref-PSM_8-0">a</a> <a href="#cite_ref-PSM_8-1">b</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://postgres.cz/wiki/SQL/PSM_Manual">SQL/PSM</a> (manual), PostgreSQL, 20 May 2008</cite>.

</li>

<li id="cite_note-9"><a href="#cite_ref-9">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://public.commandprompt.com/projects/plphp/">"PL/PHP"</a>, Community, Command prompt</cite>.

</li>

<li id="cite_note-10"><a href="#cite_ref-10">^</a> <cite id="CITEREFConway" class="citation cs2">Conway, Joe, <a rel="nofollow" class="external text" href="https://web.archive.org/web/20140619175406/http://www.joeconway.com/plr/">PL/R</a>, archived from <a rel="nofollow" class="external text" href="http://www.joeconway.com/plr/">the original</a> on 2014-06-19, retrieved 2005-02-25</cite>.

</li>

<li id="cite_note-11"><a href="#cite_ref-11">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="https://github.com/knu/postgresql-plruby">PL/Ruby</a> (project), Ruby lang, 2018-11-23</cite>.

</li>

<li id="cite_note-12"><a href="#cite_ref-12">^</a> <cite id="CITEREFHaverbeke2024" class="citation cs2">Haverbeke, Marijn (2024-05-27), <a rel="nofollow" class="external text" href="https://github.com/marijnh/Postmodern">marijnh/Postmodern</a>, retrieved 2024-06-01</cite>

</li>

<cite class="citation cs2"><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/9.0/static/parser-stage.html">"Parser stage"</a>, Docs (9 ed.), PostgreSQL</cite>

</li>

<li id="cite_note-proposal-14"><a href="#cite_ref-proposal_14-0">^</a> <cite id="CITEREFStehule" class="citation cs2">Stehule, P, <a rel="nofollow" class="external text" href="https://www.postgresql.org/message-id/CAFj8pRDWFdcjNSnwQB_3j1-rMO6b8=TmLTNBvDCSpRrOW2Dfeg@mail.gmail.com">"Proposal: PL/pgPSM for pg9.3"</a>, Hackers (mailing list), PostgreSQL</cite>.

</li>

<li id="cite_note-15"><a href="#cite_ref-15">^</a> <cite class="citation cs2"><a rel="nofollow" class="external text" href="http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/limits.htm#LNPLS018">"C. PL/SQL Program Limits"</a>, PL/SQL Language Reference, Database Online Documentation (11g Release 1 (11.1) ed.), Oracle, p. 87, <q>PL/SQL is based on the programming language Ada.</q></cite>

</li>

<li id="cite_note-16"><a href="#cite_ref-16">^</a> <cite class="citation cs2">"1", <a rel="nofollow" class="external text" href="http://cdn.oreillystatic.com/en/assets/1/event/36/Stored%2520Procedures%2520-%2520Part%25201%2520Presentation.odp">Stored procedures</a> (ODP), O’Reilly</cite>.

</li>

</ol></div></div>

<div class="mw-heading mw-heading2"><h2 id="External_links">External links</h2></div>

<ul><li><a rel="nofollow" class="external text" href="https://www.postgresql.org/docs/current/static/plpgsql.html">Official PL/pgSQL documentation</a></li></ul>

/* start https://en.wikipedia.org/ */

.mw-parser-output .asbox{position:relative;overflow:hidden}.mw-parser-output .asbox table{background:transparent}.mw-parser-output .asbox p{margin:0}.mw-parser-output .asbox p+p{margin-top:0.25em}.mw-parser-output .asbox-body{font-style:italic}.mw-parser-output .asbox-note{font-size:smaller}.mw-parser-output .asbox .navbar{position:absolute;top:-0.75em;right:1em;display:none}.mw-parser-output :not(p):not(.asbox)+style+.asbox,.mw-parser-output :not(p):not(.asbox)+link+.asbox{margin-top:3em}

/* end https://en.wikipedia.org/ */

</style></div><div><div class="zim-footer">

This article is issued from <a class="external text" title="Last edited on 2025-07-25" href="https://en.wikipedia.org/wiki/?title=PL%2FpgSQL&oldid=1302464137">Wikipedia</a>. The text is available under <a class="external text" href="https://creativecommons.org/licenses/by-sa/4.0/deed.en">Creative Commons Attribution-Share Alike 4.0</a> unless otherwise noted. Additional terms may apply for the media files.

</div>

</div></div>

</div>

</main>

</div>

</body></html>